package service;

import com.fasterxml.jackson.databind.ObjectMapper;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

@WebServlet("/ss")
public class Semester_Class_G_Servlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        List<Semester_Gender>   stats=getSemester_Gender();
        ObjectMapper mapper = new ObjectMapper();
        String json = mapper.writeValueAsString(stats);

        resp.setContentType("application/json");
        resp.setCharacterEncoding("UTF-8");
        resp.getWriter().write(json);
    }

    private List<Semester_Gender> getSemester_Gender(){
        List<Semester_Gender> stats=new ArrayList<>();
        String url = "jdbc:mysql://localhost:3306/stats?serverTimezone=Asia/Shanghai&useSSL=false";
        String user = "root";
        String password = "123456";

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        try (Connection conn = DriverManager.getConnection(url, user, password);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(" select semester,class_id,gender,sum(count) as total from semester_class group by semester,class_id,gender;")) {

            while (rs.next()) {
                stats.add(new Semester_Gender(rs.getString("semester"),rs.getString("class_id"), rs.getString("gender"), rs.getInt("total")));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }


        return stats;
    }


    public static class Semester_Gender{
        private String semester;
        private String class_id;
        private String gender;
        private int count;

        public Semester_Gender(String semester, String class_id, String gender, int count) {
            this.semester = semester;
            this.class_id = class_id;
            this.gender = gender;
            this.count = count;
        }

        public String getSemester() {
            return semester;
        }

        public String getClass_id() {
            return class_id;
        }

        public String getGender() {
            return gender;
        }

        public int getCount() {
            return count;
        }
    }

}
